from datetime import datetime
from typing import Optional, List
from sqlmodel import Field, SQLModel, Relationship
from enum import Enum
import json


class UserRole(str, Enum):
    """用户角色枚举"""
    CUSTOMER = "customer"
    ADMIN = "admin"
    MANUFACTURER = "manufacturer"


class OrderStatus(str, Enum):
    """订单状态枚举"""
    PENDING = "pending"  # 待支付
    PAID = "paid"  # 已支付
    PROCESSING = "processing"  # 生产中
    SHIPPED = "shipped"  # 已发货
    DELIVERED = "delivered"  # 已送达
    CANCELLED = "cancelled"  # 已取消


class JobStatus(str, Enum):
    """生产任务状态枚举"""
    PENDING = "pending"  # 待处理
    ASSIGNED = "assigned"  # 已分配
    IN_PROGRESS = "in_progress"  # 生产中
    COMPLETED = "completed"  # 已完成
    FAILED = "failed"  # 失败


class FileType(str, Enum):
    """文件类型枚举"""
    IMAGE = "image"  # 原始图片
    MODEL_3D = "model_3d"  # 3D模型文件
    PREVIEW = "preview"  # 预览图
    PRODUCTION = "production"  # 生产文件


class Model3DStatus(str, Enum):
    """3D模型状态枚举"""
    PENDING = "pending"  # 待生成
    GENERATING = "generating"  # 生成中
    GENERATED = "generated"  # 已生成
    OPTIMIZING = "optimizing"  # 优化中
    OPTIMIZED = "optimized"  # 已优化
    UPLOADING = "uploading"  # 上传中
    UPLOADED = "uploaded"  # 已上传
    FAILED = "failed"  # 生成失败
    
    @classmethod
    def _missing_(cls, value):
        """支持大小写不敏感的比较和数据库兼容性"""
        if isinstance(value, str):
            value_lower = value.lower()
            # 直接匹配小写值
            for member in cls:
                if member.value == value_lower:
                    return member
            # 如果数据库中有大写值，也支持
            value_upper = value.upper()
            for member in cls:
                if member.value.upper() == value_upper:
                    return member
        return super()._missing_(value)


class ShopifyResourceType(str, Enum):
    """Shopify资源类型枚举"""
    PRODUCT = "product"  # 产品
    ORDER = "order"  # 订单
    VARIANT = "variant"  # 变体
    ASSET = "asset"  # 资源文件
    METAFIELD = "metafield"  # 元字段


class FileProcessStatus(str, Enum):
    """文件处理状态枚举"""
    UPLOADED = "uploaded"  # 已上传
    PROCESSING = "processing"  # 处理中
    PROCESSED = "processed"  # 已处理
    FAILED = "failed"  # 处理失败
    ARCHIVED = "archived"  # 已归档


# 用户表
class User(SQLModel, table=True):
    __tablename__ = "users"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str = Field(unique=True, index=True, description="用户邮箱")
    username: str = Field(unique=True, index=True, description="用户名")
    hashed_password: str = Field(description="加密密码")
    full_name: Optional[str] = Field(default=None, description="全名")
    phone: Optional[str] = Field(default=None, description="手机号")
    role: UserRole = Field(default=UserRole.CUSTOMER, description="用户角色")
    is_active: bool = Field(default=True, description="是否激活")
    avatar_url: Optional[str] = Field(default=None, description="头像URL")
    
    created_at: datetime = Field(default_factory=datetime.utcnow, description="创建时间")
    updated_at: datetime = Field(default_factory=datetime.utcnow, description="更新时间")
    
    # 关联关系
    orders: List["Order"] = Relationship(back_populates="user")
    uploaded_images: List["ProductImage"] = Relationship(back_populates="uploader")


# 商品分类表
class Category(SQLModel, table=True):
    __tablename__ = "categories"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="分类名称")
    description: Optional[str] = Field(default=None, description="分类描述")
    image_url: Optional[str] = Field(default=None, description="分类图片URL")
    sort_order: int = Field(default=0, description="排序")
    is_active: bool = Field(default=True, description="是否激活")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    
    # 关联关系
    products: List["Product"] = Relationship(back_populates="category")


# 系统配置表
class SystemConfig(SQLModel, table=True):
    __tablename__ = "system_configs"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    config_key: str = Field(unique=True, description="配置键")
    config_value: str = Field(description="配置值")
    description: Optional[str] = Field(default=None, description="配置描述")
    config_type: str = Field(default="string", description="配置类型")
    is_sensitive: bool = Field(default=False, description="是否敏感信息")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)


# 商品表
class Product(SQLModel, table=True):
    __tablename__ = "products"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="商品名称")
    description: Optional[str] = Field(default=None, description="商品描述")
    price: float = Field(description="价格")
    category_id: int = Field(foreign_key="categories.id", description="分类ID")
    
    # Shopify相关字段
    shopify_product_id: Optional[str] = Field(default=None, description="Shopify产品ID")
    shopify_variant_id: Optional[str] = Field(default=None, description="Shopify变体ID")
    
    # 3D模板相关
    template_3d_url: Optional[str] = Field(default=None, description="3D模板文件URL")
    default_material: Optional[str] = Field(default=None, description="默认材质")
    available_materials: Optional[str] = Field(default=None, description="可用材质(JSON)")
    
    is_active: bool = Field(default=True, description="是否激活")
    stock_quantity: int = Field(default=0, description="库存数量")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    
    # 关联关系
    category: Category = Relationship(back_populates="products")
    images: List["ProductImage"] = Relationship(back_populates="product")
    orders: List["Order"] = Relationship(back_populates="product")


# 商品图片表
class ProductImage(SQLModel, table=True):
    __tablename__ = "product_images"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    product_id: int = Field(foreign_key="products.id", description="商品ID")
    uploader_id: int = Field(foreign_key="users.id", description="上传用户ID")
    
    original_filename: str = Field(description="原始文件名")
    file_url: str = Field(description="文件URL")
    file_size: int = Field(description="文件大小(字节)")
    mime_type: str = Field(description="MIME类型")
    
    width: Optional[int] = Field(default=None, description="图片宽度")
    height: Optional[int] = Field(default=None, description="图片高度")
    
    is_primary: bool = Field(default=False, description="是否主图")
    
    # 文件处理状态
    process_status: FileProcessStatus = Field(default=FileProcessStatus.UPLOADED, description="处理状态")
    process_error: Optional[str] = Field(default=None, description="处理错误信息")
    
    # 3D模型生成相关
    model_generation_params: Optional[str] = Field(default=None, description="3D模型生成参数(JSON)")
    generated_model_id: Optional[int] = Field(default=None, description="生成的3D模型ID")
    
    # AI分析结果
    ai_analysis_result: Optional[str] = Field(default=None, description="AI分析结果(JSON)")
    object_detected: Optional[str] = Field(default=None, description="检测到的物体")
    confidence_score: Optional[float] = Field(default=None, description="置信度分数")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    processed_at: Optional[datetime] = Field(default=None, description="处理完成时间")
    
    # 关联关系
    product: Product = Relationship(back_populates="images")
    uploader: User = Relationship(back_populates="uploaded_images")
    generated_models: List["Model3D"] = Relationship(back_populates="source_image")


# 3D模型表
class Model3D(SQLModel, table=True):
    __tablename__ = "3d_models"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    product_id: int = Field(foreign_key="products.id", description="商品ID")
    source_image_id: int = Field(foreign_key="product_images.id", description="源图片ID")
    
    # 文件信息
    model_file_url: str = Field(description="3D模型文件URL")
    preview_image_url: Optional[str] = Field(default=None, description="预览图URL")
    file_format: str = Field(description="文件格式(glb, obj, stl等)")
    file_size: int = Field(description="文件大小(字节)")
    original_filename: str = Field(description="原始文件名")
    storage_path: str = Field(description="存储路径")
    
    # 模型状态管理
    status: str = Field(default="pending", description="模型状态")  # 使用字符串存储状态
    error_message: Optional[str] = Field(default=None, description="错误信息")
    
    # 生成参数和元数据
    parameters: Optional[str] = Field(default=None, description="生成参数(JSON)")
    ai_model_used: Optional[str] = Field(default=None, description="使用的AI模型")
    generation_time: Optional[int] = Field(default=None, description="生成时间(秒)")
    model_metadata: Optional[str] = Field(default=None, description="模型元数据(JSON)")
    
    # 几何信息
    vertices_count: Optional[int] = Field(default=None, description="顶点数")
    faces_count: Optional[int] = Field(default=None, description="面数")
    dimensions: Optional[str] = Field(default=None, description="尺寸(JSON: {x, y, z})")
    bounding_box: Optional[str] = Field(default=None, description="边界框(JSON)")
    
    # Shopify集成
    shopify_asset_id: Optional[str] = Field(default=None, description="Shopify资源ID")
    shopify_asset_url: Optional[str] = Field(default=None, description="Shopify资源URL")
    shopify_product_id: Optional[str] = Field(default=None, description="Shopify产品ID")
    shopify_variant_id: Optional[str] = Field(default=None, description="Shopify变体ID")
    shopify_metafield_id: Optional[str] = Field(default=None, description="Shopify元字段ID")
    
    # 优化信息
    is_optimized: bool = Field(default=False, description="是否已优化")
    optimization_params: Optional[str] = Field(default=None, description="优化参数(JSON)")
    optimization_time: Optional[int] = Field(default=None, description="优化时间(秒)")
    
    # 版本控制
    version: int = Field(default=1, description="版本号")
    parent_model_id: Optional[int] = Field(default=None, description="父模型ID")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    generated_at: Optional[datetime] = Field(default=None, description="生成完成时间")
    optimized_at: Optional[datetime] = Field(default=None, description="优化完成时间")
    uploaded_at: Optional[datetime] = Field(default=None, description="上传完成时间")
    
    # 关联关系
    source_image: ProductImage = Relationship(back_populates="generated_models")
    product: Product = Relationship()
    orders: List["Order"] = Relationship(back_populates="model_3d")


# 厂商表
class Manufacturer(SQLModel, table=True):
    __tablename__ = "manufacturers"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    
    company_name: str = Field(description="公司名称")
    contact_person: str = Field(description="联系人")
    email: str = Field(description="邮箱")
    phone: str = Field(description="电话")
    address: str = Field(description="地址")
    
    # 生产能力
    supported_materials: str = Field(description="支持材质(JSON)")
    max_production_capacity: int = Field(description="最大生产能力")
    production_lead_time: int = Field(description="生产周期(天)")
    
    # API配置
    api_endpoint: Optional[str] = Field(default=None, description="API端点")
    api_key: Optional[str] = Field(default=None, description="API密钥")
    
    is_active: bool = Field(default=True, description="是否激活")
    rating: Optional[float] = Field(default=None, description="评分")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    
    # 关联关系
    production_jobs: List["ProductionJob"] = Relationship(back_populates="manufacturer")


# 订单表
class Order(SQLModel, table=True):
    __tablename__ = "orders"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    order_number: str = Field(unique=True, index=True, description="订单号")
    
    user_id: int = Field(foreign_key="users.id", description="用户ID")
    product_id: int = Field(foreign_key="products.id", description="商品ID")
    model_3d_id: int = Field(foreign_key="3d_models.id", description="3D模型ID")
    
    # 订单详情
    quantity: int = Field(description="数量")
    unit_price: float = Field(description="单价")
    total_amount: float = Field(description="总金额")
    
    # 定制参数
    custom_material: Optional[str] = Field(default=None, description="定制材质")
    custom_size: Optional[str] = Field(default=None, description="定制尺寸")
    custom_color: Optional[str] = Field(default=None, description="定制颜色")
    custom_params: Optional[str] = Field(default=None, description="其他定制参数(JSON)")
    
    # 状态管理
    status: OrderStatus = Field(default=OrderStatus.PENDING, description="订单状态")
    payment_status: str = Field(default="pending", description="支付状态")
    payment_method: Optional[str] = Field(default=None, description="支付方式")
    
    # Shopify相关
    shopify_order_id: Optional[str] = Field(default=None, description="Shopify订单ID")
    
    # 物流信息
    shipping_address: str = Field(description="收货地址")
    shipping_name: str = Field(description="收货人姓名")
    shipping_phone: str = Field(description="收货人电话")
    tracking_number: Optional[str] = Field(default=None, description="快递单号")
    
    # 时间戳
    created_at: datetime = Field(default_factory=datetime.utcnow)
    paid_at: Optional[datetime] = Field(default=None, description="支付时间")
    shipped_at: Optional[datetime] = Field(default=None, description="发货时间")
    delivered_at: Optional[datetime] = Field(default=None, description="送达时间")
    
    # 关联关系
    user: User = Relationship(back_populates="orders")
    product: Product = Relationship(back_populates="orders")
    model_3d: Model3D = Relationship(back_populates="orders")
    production_jobs: List["ProductionJob"] = Relationship(back_populates="order")


# 生产任务表
class ProductionJob(SQLModel, table=True):
    __tablename__ = "production_jobs"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    job_number: str = Field(unique=True, index=True, description="任务编号")
    
    order_id: int = Field(foreign_key="orders.id", description="订单ID")
    manufacturer_id: int = Field(foreign_key="manufacturers.id", description="厂商ID")
    
    # 生产文件
    production_file_url: str = Field(description="生产文件URL")
    file_format: str = Field(description="文件格式")
    material_specification: str = Field(description="材质规格")
    
    # 生产参数
    quantity: int = Field(description="数量")
    priority: int = Field(default=1, description="优先级")
    estimated_completion_date: Optional[datetime] = Field(default=None, description="预计完成时间")
    actual_completion_date: Optional[datetime] = Field(default=None, description="实际完成时间")
    
    # 状态管理
    status: JobStatus = Field(default=JobStatus.PENDING, description="任务状态")
    progress_percentage: int = Field(default=0, description="进度百分比")
    
    # 成本核算
    production_cost: Optional[float] = Field(default=None, description="生产成本")
    material_cost: Optional[float] = Field(default=None, description="材料成本")
    labor_cost: Optional[float] = Field(default=None, description="人工成本")
    
    # 质检信息
    quality_check_passed: Optional[bool] = Field(default=None, description="质检是否通过")
    quality_notes: Optional[str] = Field(default=None, description="质检备注")
    
    # 时间戳
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    assigned_at: Optional[datetime] = Field(default=None, description="分配时间")
    started_at: Optional[datetime] = Field(default=None, description="开始时间")
    completed_at: Optional[datetime] = Field(default=None, description="完成时间")
    
    # 关联关系
    order: Order = Relationship(back_populates="production_jobs")
    manufacturer: Manufacturer = Relationship(back_populates="production_jobs")
    
    def dict(self, **kwargs):
        """重写dict方法，处理日期时间序列化"""
        data = super().dict(**kwargs)
        
        # 处理日期时间字段
        datetime_fields = [
            'created_at', 'updated_at', 'assigned_at', 'started_at', 
            'completed_at', 'estimated_completion_date', 'actual_completion_date'
        ]
        
        for field in datetime_fields:
            if field in data and data[field] is not None:
                if isinstance(data[field], datetime):
                    data[field] = data[field].isoformat()
        
        return data


# 3D模型文件上传表
class Model3DUpload(SQLModel, table=True):
    __tablename__ = "model3d_uploads"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: Optional[int] = Field(default=None, foreign_key="users.id", description="上传用户ID")
    product_id: Optional[int] = Field(default=None, foreign_key="products.id", description="商品ID")
    
    # 上传文件信息
    original_filename: str = Field(description="原始文件名")
    file_url: str = Field(description="文件URL")
    file_size: int = Field(description="文件大小(字节)")
    file_format: str = Field(description="文件格式")
    mime_type: str = Field(description="MIME类型")
    storage_path: str = Field(description="存储路径")
    
    # 处理状态
    status: FileProcessStatus = Field(default=FileProcessStatus.UPLOADED, description="处理状态")
    error_message: Optional[str] = Field(default=None, description="错误信息")
    
    # 3D模型生成参数
    generation_params: Optional[str] = Field(default=None, description="生成参数(JSON)")
    ai_model_preference: Optional[str] = Field(default=None, description="AI模型偏好")
    
    # 处理结果
    generated_model_id: Optional[int] = Field(default=None, description="生成的3D模型ID")
    processing_time: Optional[int] = Field(default=None, description="处理时间(秒)")
    
    # Shopify集成状态
    shopify_sync_status: str = Field(default="pending", description="Shopify同步状态")
    shopify_product_id: Optional[str] = Field(default=None, description="Shopify产品ID")
    shopify_errors: Optional[str] = Field(default=None, description="Shopify错误信息")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)
    processing_started_at: Optional[datetime] = Field(default=None, description="处理开始时间")
    processing_completed_at: Optional[datetime] = Field(default=None, description="处理完成时间")
    
    # 关联关系
    user: User = Relationship()
    product: Product = Relationship()


# Shopify同步记录表
class ShopifySyncLog(SQLModel, table=True):
    __tablename__ = "shopify_sync_logs"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # 关联信息
    model_3d_id: Optional[int] = Field(default=None, description="3D模型ID")
    upload_id: Optional[int] = Field(default=None, description="上传记录ID")
    order_id: Optional[int] = Field(default=None, description="订单ID")
    
    # Shopify信息
    shopify_resource_type: ShopifyResourceType = Field(description="Shopify资源类型")
    shopify_resource_id: str = Field(description="Shopify资源ID")
    shopify_operation: str = Field(description="操作类型(create, update, delete)")
    
    # 同步详情
    request_data: Optional[str] = Field(default=None, description="请求数据(JSON)")
    response_data: Optional[str] = Field(default=None, description="响应数据(JSON)")
    sync_status: str = Field(description="同步状态")
    error_message: Optional[str] = Field(default=None, description="错误信息")
    
    # 性能信息
    response_time: Optional[int] = Field(default=None, description="响应时间(毫秒)")
    retry_count: int = Field(default=0, description="重试次数")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    synced_at: Optional[datetime] = Field(default=None, description="同步完成时间")


# 生产统计表
class ProductionStats(SQLModel, table=True):
    __tablename__ = "production_stats"
    
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # 统计维度
    stat_date: datetime = Field(description="统计日期")
    manufacturer_id: Optional[int] = Field(default=None, description="厂商ID")
    product_id: Optional[int] = Field(default=None, description="产品ID")
    
    # 3D模型统计
    models_generated: int = Field(default=0, description="生成的3D模型数")
    models_optimized: int = Field(default=0, description="优化的3D模型数")
    models_uploaded: int = Field(default=0, description="上传的3D模型数")
    models_failed: int = Field(default=0, description="失败的3D模型数")
    
    # 订单统计
    orders_received: int = Field(default=0, description="收到的订单数")
    orders_completed: int = Field(default=0, description="完成的订单数")
    orders_failed: int = Field(default=0, description="失败的订单数")
    
    # 生产统计
    production_jobs: int = Field(default=0, description="生产任务数")
    production_completed: int = Field(default=0, description="完成的生产任务数")
    production_failed: int = Field(default=0, description="失败的生产任务数")
    
    # 财务统计
    total_revenue: float = Field(default=0.0, description="总收入")
    total_cost: float = Field(default=0.0, description="总成本")
    
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)